﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for PostBO
    /// </summary>
    public class PostBO : DBConnectionBO
    {
        public PostBO()
            : base()
        {

        }

        /// <summary>
        /// GetListPosts
        /// </summary>
        /// <returns>List Post</returns>
        public List<Post> GetListPosts()
        {
            var listPosts = new List<Post>();
            const string query = "SELECT * FROM [PrivateBlog].[dbo].[Posts] WHERE Status = 1 Order By LastDateUpdated DESC ";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var post = new Post
                    {
                        Id = (string)dataTable.Rows[i][0],
                        UniqueId = (string)dataTable.Rows[i][1],
                        IdUser = (int)dataTable.Rows[i][2],
                        IdCategory = (string)dataTable.Rows[i][3],
                        Title = (string)dataTable.Rows[i][4],
                        Description = (string)dataTable.Rows[i][5],
                        Content = (string)dataTable.Rows[i][6],
                        ImagePanorama = (string)dataTable.Rows[i][7],
                        ImageThumbnail = (string)dataTable.Rows[i][8],
                        View = (int)dataTable.Rows[i][9],
                        Source = (string)dataTable.Rows[i][10],
                        LinkDownLoad = (string)dataTable.Rows[i][11],
                        Tags = (string)dataTable.Rows[i][12],
                        LastDateUpdated = (DateTime)dataTable.Rows[i][13],
                        LastUpdatedBy = (string)dataTable.Rows[i][14],
                        FeedBack = (string)dataTable.Rows[i][15],
                        Status = Convert.ToInt32(dataTable.Rows[i][16])
                    };
                listPosts.Add(post);
            }

            return listPosts;
        }

        /// <summary>
        /// Add new a post
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public int AddPost(Post post)
        {
            var query = "INSERT INTO [PrivateBlog].[dbo].[Posts]([Id],[UniqueId],[IdUser],[IdCategory],[Title],[Description]," +
                        "[Content],[ImagePanorama],[ImageThumbnail],[View],[Source],[LinkDownLoad],[Tags],[LastDateUpdated]," +
                        "[LastUpdatedBy],[FeedBack],[Status]) VALUES('"
                        + post.Id + "', '"
                        + post.UniqueId + "', "
                        + post.IdUser + ", '"
                        + post.IdCategory + "', N'"
                        + post.Title + "', N'"
                        + post.Description + "', N'"
                        + post.Content + "', N'"
                        + post.ImagePanorama + "', N'"
                        + post.ImageThumbnail + "', 0, N'"                        
                        + post.Source + "', N'"
                        + post.LinkDownLoad + "', N'"
                        + post.Tags + "', "
                        + "GETDATE(), N'"
                        + post.LastUpdatedBy + "', N'"
                        + post.FeedBack + "', "
                        + post.Status
                        + ")";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }

        }

        /// <summary>
        /// Edit post
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public int EditPost(Post post)
        {
            var query = "UPDATE [PrivateBlog].[dbo].[Posts]" +
                        " SET IdUser = " + post.IdUser + ", " +
                        "UniqueId = '" + post.UniqueId + "', " +
                        "IdCategory = '" + post.IdCategory + "'," +
                        "Title = N'" + post.Title + "'," +
                        "Description = N'" + post.Description + "'," +
                        "Content = N'" + post.Content + "'," +
                        "ImagePanorama = N'" + post.ImagePanorama + "'," +
                        "ImageThumbnail = N'" + post.ImageThumbnail + "'," +                        
                        "Source = N'" + post.Source + "'," +
                        "LinkDownLoad = N'" + post.LinkDownLoad + "'," +
                        "Tags = N'" + post.Tags + "'," +
                        "LastDateUpdated = GETDATE(), " +
                        "LastUpdatedBy = N'" + post.LastUpdatedBy + "'," +
                        "FeedBack = N'" + post.FeedBack + "'," +
                        "Status = " + post.Status + " " +
                        " WHERE Id like '" + post.Id + "'";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete post
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public int DeletePost(Post post)
        {
            var query = "DELETE FROM [PrivateBlog].[dbo].[Posts] " +
                        " WHERE Id like N'" + post.Id + "'";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }
        /// <summary>
        /// Find post by unique id
        /// </summary>
        /// <param name="uniqueId"></param>
        /// <returns></returns>
        public List<Post> FindPostbyUniqueId(string uniqueId)
        {
            var listPosts = new List<Post>();
            var query = "SELECT TOP 1 * FROM [PrivateBlog].[dbo].[Posts] WHERE UniqueId like '"+ uniqueId +"' and Status = 1 Order By LastDateUpdated DESC";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var post = new Post
                {
                    Id = (string)dataTable.Rows[i][0],
                    UniqueId = (string)dataTable.Rows[i][1],
                    IdUser = (int)dataTable.Rows[i][2],
                    IdCategory = (string)dataTable.Rows[i][3],
                    Title = (string)dataTable.Rows[i][4],
                    Description = (string)dataTable.Rows[i][5],
                    Content = (string)dataTable.Rows[i][6],
                    ImagePanorama = (string)dataTable.Rows[i][7],
                    ImageThumbnail = (string)dataTable.Rows[i][8],
                    View = (int)dataTable.Rows[i][9],
                    Source = (string)dataTable.Rows[i][10],
                    LinkDownLoad = (string)dataTable.Rows[i][11],
                    Tags = (string)dataTable.Rows[i][12],
                    LastDateUpdated = (DateTime)dataTable.Rows[i][13],
                    LastUpdatedBy = (string)dataTable.Rows[i][14],
                    FeedBack = (string)dataTable.Rows[i][15],
                    Status = Convert.ToInt32(dataTable.Rows[i][16])
                };
                listPosts.Add(post);
            }

            return listPosts;
        }

        /// <summary>
        /// Find post by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Post> FindPostById(string id)
        {
            var listPosts = new List<Post>();
            var query = "SELECT TOP 1 * FROM [PrivateBlog].[dbo].[Posts] WHERE Id like '" + id + "' and Status = 1 Order By LastDateUpdated DESC";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var post = new Post
                {
                    Id = (string)dataTable.Rows[i][0],
                    UniqueId = (string)dataTable.Rows[i][1],
                    IdUser = (int)dataTable.Rows[i][2],
                    IdCategory = (string)dataTable.Rows[i][3],
                    Title = (string)dataTable.Rows[i][4],
                    Description = (string)dataTable.Rows[i][5],
                    Content = (string)dataTable.Rows[i][6],
                    ImagePanorama = (string)dataTable.Rows[i][7],
                    ImageThumbnail = (string)dataTable.Rows[i][8],
                    View = (int)dataTable.Rows[i][9],
                    Source = (string)dataTable.Rows[i][10],
                    LinkDownLoad = (string)dataTable.Rows[i][11],
                    Tags = (string)dataTable.Rows[i][12],
                    LastDateUpdated = (DateTime)dataTable.Rows[i][13],
                    LastUpdatedBy = (string)dataTable.Rows[i][14],
                    FeedBack = (string)dataTable.Rows[i][15],
                    Status = Convert.ToInt32(dataTable.Rows[i][16])
                };
                listPosts.Add(post);
            }

            return listPosts;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public int UpdateView(Post post)
        {
            var view = post.View + 1;
            var query = "UPDATE [PrivateBlog].[dbo].[Posts]" +
                        " SET [View] = " + view +
                        " WHERE Id like '" + post.Id + "'";;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Order by view
        /// </summary>
        /// <returns></returns>
        public List<Post> GetListPostsPopularByView()
        {
            var listPosts = new List<Post>();
            const string query = "SELECT TOP 9 * FROM [PrivateBlog].[dbo].[Posts] WHERE Status = 1 Order By [View] DESC, LastDateUpdated DESC ";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var post = new Post
                {
                    Id = (string)dataTable.Rows[i][0],
                    UniqueId = (string)dataTable.Rows[i][1],
                    IdUser = (int)dataTable.Rows[i][2],
                    IdCategory = (string)dataTable.Rows[i][3],
                    Title = (string)dataTable.Rows[i][4],
                    Description = (string)dataTable.Rows[i][5],
                    Content = (string)dataTable.Rows[i][6],
                    ImagePanorama = (string)dataTable.Rows[i][7],
                    ImageThumbnail = (string)dataTable.Rows[i][8],
                    View = (int)dataTable.Rows[i][9],
                    Source = (string)dataTable.Rows[i][10],
                    LinkDownLoad = (string)dataTable.Rows[i][11],
                    Tags = (string)dataTable.Rows[i][12],
                    LastDateUpdated = (DateTime)dataTable.Rows[i][13],
                    LastUpdatedBy = (string)dataTable.Rows[i][14],
                    FeedBack = (string)dataTable.Rows[i][15],
                    Status = Convert.ToInt32(dataTable.Rows[i][16])
                };
                listPosts.Add(post);
            }

            return listPosts;
        }
        
        /// <summary>
        /// Get list post by condition IdCategory and order by LastDateUpdated
        /// </summary>
        /// <param name="idCat"></param>
        /// <returns></returns>
        public List<Post> GetListPostsByIdCategory(string idCat)
        {
            var listPosts = new List<Post>();
            var query = "SELECT * FROM [PrivateBlog].[dbo].[Posts] WHERE Status = 1 and IdCategory like '" + idCat + "' Order By LastDateUpdated DESC ";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var post = new Post
                {
                    Id = (string)dataTable.Rows[i][0],
                    UniqueId = (string)dataTable.Rows[i][1],
                    IdUser = (int)dataTable.Rows[i][2],
                    IdCategory = (string)dataTable.Rows[i][3],
                    Title = (string)dataTable.Rows[i][4],
                    Description = (string)dataTable.Rows[i][5],
                    Content = (string)dataTable.Rows[i][6],
                    ImagePanorama = (string)dataTable.Rows[i][7],
                    ImageThumbnail = (string)dataTable.Rows[i][8],
                    View = (int)dataTable.Rows[i][9],
                    Source = (string)dataTable.Rows[i][10],
                    LinkDownLoad = (string)dataTable.Rows[i][11],
                    Tags = (string)dataTable.Rows[i][12],
                    LastDateUpdated = (DateTime)dataTable.Rows[i][13],
                    LastUpdatedBy = (string)dataTable.Rows[i][14],
                    FeedBack = (string)dataTable.Rows[i][15],
                    Status = Convert.ToInt32(dataTable.Rows[i][16])
                };
                listPosts.Add(post);
            }

            return listPosts;
        }

    }
}